 <?php
class QOOSData
{	
	public function __construct($p_strCountry)
	{
		$this->_country	= $p_strCountry;
		$this->_table	= strtolower($p_strCountry).'_oos_data';
				
		$this->_db		= new Common_Db('wms_'.$this->_country);
		$this->_dbbob		= new Common_Db('wms_'.$this->_country . '_bob');
	}
	
	function __destruct() 
	{
		$this->_db->closeConnection();
		$this->_dbbob->closeConnection();
	}
		
	public function total()
	{
		$strQuery	= '
			SELECT COUNT(id) `total`
			FROM '.$this->_table;
		$db 		= Zend_Registry::get('db');
		$arrResult 	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}
	
	public function get_by_id($p_intId)
	{
		$strQuery		= 'SELECT * FROM '.$this->_table.' WHERE id='.intval($p_intId);
		$db 			= Zend_Registry::get('db');
		$resultValue	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function insert($p_arrParams)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->insert($this->_table, $p_arrParams);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function update($p_arrParams, $p_intId)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->update($this->_table, $p_arrParams, 'id='.intval($p_intId));
		$db->closeConnection();
		return $resultValue;
	}
	
	public function reset()
	{
		$strQuery	= 'TRUNCATE '.$this->_table;
		$db 		= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function getOOSDataFromOMS()
	{
		$objSupplier = new QSupplier($this->_country);
		$arrSupplier = $objSupplier->getWeekday();
		$limitRecord = 1000;
		
		$db	= Zend_Registry::get('db');	
		
		$strIdSupplier ="" ; 
		foreach($arrSupplier as $item)
		{
			$strIdSupplier =$strIdSupplier . $item['id'] . ',';
		}
		$strIdSupplier = substr($strIdSupplier, 0 , -1);
		
		// get asl data
		$aslQuery =	'SELECT p.sku, count(1) as count 
					FROM oms_live.wms_inventory i 
						Inner Join oms_live.ims_product p On i.fk_product = p.id_product 
						Inner Join oms_live.wms_inventory_status q on q.id_inventory_status = i.fk_inventory_status 
					WHERE q.id_inventory_status = 1 
					group by p.sku;';
					
		$aslArrData =$this->_db->fetchAll($aslQuery);
		
		// get intransit data
		$intransitQuery ='SELECT q.sku, count(i.quantity) as  quantity
						FROM oms_live.ims_purchase_order_item  i 
						Inner Join oms_live.ims_supplier_product p On 
						i.fk_supplier_product = p.id_supplier_product 
						Inner Join oms_live.ims_product q 
						On p.fk_product = q.id_product
						Inner join oms_live.ims_purchase_order r
						on i.fk_purchase_order = r.id_purchase_order
						Inner join oms_live.ims_purchase_order_status s
						on r.fk_purchase_order_status = s.id_purchase_order_status
						WHERE i.fk_purchase_order_item_status=2
						and r.fk_purchase_order_status In ("1","3","6","5")
						AND i.fk_sales_order_item  is null
					 group by q.sku;';
		
		$intransitArrData =$this->_db->fetchAll($intransitQuery);
				
		$arrDataForecasts =array();
		if($strIdSupplier)
		{	
			//count total record for get 		
			$strQuery	= 'SELECT count(1) as total
						  FROM catalog_simple cs
						  JOIN catalog_config cc ON cc.id_catalog_config = cs.fk_catalog_config
						  JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_config = cc.id_catalog_config
						  JOIN catalog_category cat ON cccat.fk_catalog_category = cat.id_catalog_category
						 WHERE cc.status = "active"
						   AND cc.fk_catalog_supplier NOT IN (' . $strIdSupplier . ')
						   AND cat.id_catalog_category <> 1
						   AND cc.status_supplier_config = "active"
						   AND cc.pet_approved = 1
						   AND EXISTS (SELECT 1
										 FROM catalog_brand cb
										WHERE cb.id_catalog_brand = cc.fk_catalog_brand
										  AND cb.status = "active")
						   AND EXISTS (SELECT 1
										 FROM catalog_supplier sup
										WHERE sup.id_catalog_supplier = cc.fk_catalog_supplier
										  AND sup.status = "active")
						   AND EXISTS (SELECT 1
										 FROM catalog_category cat
										 JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_category = cat.id_catalog_category
										WHERE cccat.fk_catalog_config = cc.id_catalog_config
										  AND cat.status = "active")
						   AND cs.status = "active"
						   AND cs.status_supplier_simple = "active"
						   AND cs.price > 0
						   AND (cc.display_if_out_of_stock = 1
								OR
								(IFNULL((SELECT CAST(st.quantity AS SIGNED INT)
										   FROM catalog_stock st
										  WHERE st.fk_catalog_simple = cs.id_catalog_simple), 0)
								 -
								 (SELECT COUNT(1)
									FROM sales_order_item soi
								   WHERE soi.sku = cs.sku
									 AND soi.is_reserved = 1)) > 0)
						GROUP BY cc.id_catalog_config, cs.sku;';
		
			$arrDataCountOOS	= $this->_dbbob->fetchAll($strQuery);				
			
			$totalReocord = count($arrDataCountOOS);
			$totalPage = $totalReocord / $limitRecord;
			if($totalReocord % $limitRecord)
			{
				$totalPage += 1;
			} 
			
			$this->reset();		
			for($i = 0 ; $i < $totalPage; $i++)
			{	
				//get record by paging 		
				$strQuery	= 'SELECT cc.id_catalog_config, cs.sku `sku`,cc.name as product_name, cat.`name_en` categoryname, 
								cc.fk_catalog_supplier as supplier_id
						  FROM catalog_simple cs
						  JOIN catalog_config cc ON cc.id_catalog_config = cs.fk_catalog_config
						  JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_config = cc.id_catalog_config
						  JOIN catalog_category cat ON cccat.fk_catalog_category = cat.id_catalog_category
						 WHERE cc.status = "active"
						   AND cc.fk_catalog_supplier NOT IN (' . $strIdSupplier . ')
						   AND cat.id_catalog_category <> 1
						   AND cc.status_supplier_config = "active"
						   AND cc.pet_approved = 1
						   AND EXISTS (SELECT 1
										 FROM catalog_brand cb
										WHERE cb.id_catalog_brand = cc.fk_catalog_brand
										  AND cb.status = "active")
						   AND EXISTS (SELECT 1
										 FROM catalog_supplier sup
										WHERE sup.id_catalog_supplier = cc.fk_catalog_supplier
										  AND sup.status = "active")
						   AND EXISTS (SELECT 1
										 FROM catalog_category cat
										 JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_category = cat.id_catalog_category
										WHERE cccat.fk_catalog_config = cc.id_catalog_config
										  AND cat.status = "active")
						   AND cs.status = "active"
						   AND cs.status_supplier_simple = "active"
						   AND cs.price > 0
						   AND (cc.display_if_out_of_stock = 1
								OR
								(IFNULL((SELECT CAST(st.quantity AS SIGNED INT)
										   FROM catalog_stock st
										  WHERE st.fk_catalog_simple = cs.id_catalog_simple), 0)
								 -
								 (SELECT COUNT(1)
									FROM sales_order_item soi
								   WHERE soi.sku = cs.sku
									 AND soi.is_reserved = 1)) > 0)
						GROUP BY cc.id_catalog_config, cs.sku
						ORDER BY cs.sku 
						LIMIT '. $i * $limitRecord . ',' . $limitRecord . ';';
	
				$arrDataOOS	= $this->_dbbob->fetchAll($strQuery);
				$db	= Zend_Registry::get('db');					
				//insert data to oos_table
				if($arrDataOOS)
				{	
					foreach($arrDataOOS as $data)
					{
						$inTranSit = 0;
						foreach($intransitArrData as $intransit)
						{
							if($data['sku'] == $intransit['sku'])
							{
								$inTranSit =$intransit['quantity'];
								break;
							}
						}
						$asl = 0;
						foreach($aslArrData as $aslItem)
						{
							if($data['sku'] == $aslItem['sku'])
							{
								$asl = $aslItem['count'];
								break;
							}
						}
						
						$p_arrParams = array(
							'sku' => $data['sku'],
							'product_name' =>  $data['product_name'],
							'category' => $data['categoryname'],
							'supplier_id' => $data['supplier_id'],
							'msl' => 0,
							'asl' => $asl,	
							'in_transit'=> $inTranSit,
							'created_at' => new Zend_Db_Expr('NOW()')
							);  
						$db->insert($this->_table, $p_arrParams);
					} 
					$db->closeConnection();
				}
			}
		}	
		//update msl to oos_data
		$strQuery = 'Update ' . $this->_table . ' oos Left join '. $this->_country.'_msl_data msl 
						on msl.sku = oos.sku  Set oos.msl = IFNULL(msl.msl, 0), oos.oos=
							case when oos.asl + oos.in_transit < msl.msl then "yes" else "no" end;' ;
		$db->execute($strQuery);
		
		$db->closeConnection();
	}
	
	public function getOOSDataNoPagingFromOMS()
	{
		$objSupplier = new QSupplier($this->_country);
		$arrSupplier = $objSupplier->getWeekday();
		
		$db	= Zend_Registry::get('db');			
		$strIdSupplier ="" ; 
		foreach($arrSupplier as $item)
		{
			$strIdSupplier =$strIdSupplier . $item['id'] . ',';
		}
		$strIdSupplier = substr($strIdSupplier, 0 , -1);
		
		// get asl data
		$aslQuery =	'SELECT p.sku, count(1) as count 
					FROM oms_live.wms_inventory i 
						Inner Join oms_live.ims_product p On i.fk_product = p.id_product 
						Inner Join oms_live.wms_inventory_status q on q.id_inventory_status = i.fk_inventory_status 
					WHERE q.id_inventory_status = 1 
					group by p.sku ;';
		
		$aslArrData =$this->_db->fetchAll($aslQuery);
		
		// get intransit data
		$intransitQuery ='SELECT q.sku, count(i.quantity) as  quantity
						FROM oms_live.ims_purchase_order_item  i 
						Inner Join oms_live.ims_supplier_product p On 
						i.fk_supplier_product = p.id_supplier_product 
						Inner Join oms_live.ims_product q 
						On p.fk_product = q.id_product
						Inner join oms_live.ims_purchase_order r
						on i.fk_purchase_order = r.id_purchase_order
						Inner join oms_live.ims_purchase_order_status s
						on r.fk_purchase_order_status = s.id_purchase_order_status
						WHERE i.fk_purchase_order_item_status=2
						and r.fk_purchase_order_status In ("1","3","6","5")
						AND i.fk_sales_order_item  is null
					 group by q.sku;';
		
		$intransitArrData =$this->_db->fetchAll($intransitQuery);
		
		$arrDataForecasts =array();
		if($strIdSupplier)
		{	
			$this->reset();		
			//get record by paging 		
			$strQuery	= 'SELECT cc.id_catalog_config, cs.sku `sku`,cc.name as product_name, cat.`name_en` categoryname, 
							cc.fk_catalog_supplier as supplier_id
						FROM catalog_simple cs
						JOIN catalog_config cc ON cc.id_catalog_config = cs.fk_catalog_config
						JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_config = cc.id_catalog_config
						JOIN catalog_category cat ON cccat.fk_catalog_category = cat.id_catalog_category
						WHERE cc.status = "active"
						AND cc.fk_catalog_supplier NOT IN (' . $strIdSupplier . ')
						AND cat.id_catalog_category <> 1
						AND cc.status_supplier_config = "active"
						AND cc.pet_approved = 1
						AND EXISTS (SELECT 1
										FROM catalog_brand cb
									WHERE cb.id_catalog_brand = cc.fk_catalog_brand
										AND cb.status = "active")
						AND EXISTS (SELECT 1
										FROM catalog_supplier sup
									WHERE sup.id_catalog_supplier = cc.fk_catalog_supplier
										AND sup.status = "active")
						AND EXISTS (SELECT 1
										FROM catalog_category cat
										JOIN catalog_config_has_catalog_category cccat ON cccat.fk_catalog_category = cat.id_catalog_category
									WHERE cccat.fk_catalog_config = cc.id_catalog_config
										AND cat.status = "active")
						AND cs.status = "active"
						AND cs.status_supplier_simple = "active"
						AND cs.price > 0
						AND (cc.display_if_out_of_stock = 1
							OR
							(IFNULL((SELECT CAST(st.quantity AS SIGNED INT)
										FROM catalog_stock st
										WHERE st.fk_catalog_simple = cs.id_catalog_simple), 0)
								-
								(SELECT COUNT(1)
								FROM sales_order_item soi
								WHERE soi.sku = cs.sku
									AND soi.is_reserved = 1)) > 0)
					GROUP BY cc.id_catalog_config, cs.sku;';
							 
			$arrDataOOS	=$this->_dbbob->fetchAll($strQuery);
			$db	= Zend_Registry::get('db');	
			$created_at =  new Zend_Db_Expr('NOW()');				
			//insert data to oos_table
			if($arrDataOOS)
			{	
				foreach($arrDataOOS as $data)
				{
					    $inTranSit = 0;
						foreach($intransitArrData as $intransit)
						{
							if($data['sku'] == $intransit['sku'])
							{
								$inTranSit =$intransit['quantity'];
								break;
							}
						}
						$asl = 0;
						foreach($aslArrData as $aslItem)
						{
							if($data['sku'] == $aslItem['sku'])
							{
								$asl = $aslItem['count'];
								break;
							}
					}
					
					$p_arrParams = array(
						'sku' => $data['sku'],
						'product_name' =>  $data['product_name'],
						'category' => $data['categoryname'],
						'supplier_id' => $data['supplier_id'],
						'msl' => 0,
						'asl' => $asl,	
						'in_transit'=> $inTranSit,
						'created_at' => $created_at
						);  
					$db->insert($this->_table, $p_arrParams);
				} 
			}
		}	
		//update msl to oos_data
		$strQuery = 'Update ' . $this->_table . ' oos Left join '. $this->_country.'_msl_data msl 
						on msl.sku = oos.sku  Set oos.msl =  IFNULL(msl.msl, 0) , oos.oos=
						case when oos.asl + oos.in_transit < msl.msl then "yes" else "no" end;' ;
		$db->execute($strQuery);  
		$db->closeConnection();
	}
	
	public function caculatorDateBetween()
	{
		$currentDay = CConfig::getCurrentDay();
		$currentNumber = CConfig::convertWeekDayToNumber($currentDay);
		
		$arrDataSupplier =  QSupplier::getAll($this->_country, 'suppliers');
		$arrDataBetweenDate = array();
		
		foreach($arrDataSupplier as $supplier)
		{
			$arrDataBetweenDate[$supplier['id']] =  abs(CConfig::convertWeekDayToNumber($supplier['weekday']) - $currentNumber) + 1;
		}
		return $arrDataBetweenDate;
	}
}
?>

